"""
cron: 0 */6 * * *
new Env('商品价格监控');
"""
import requests
import os
import mysql.connector
from datetime import datetime
import json

# 获取环境变量
host = os.environ.get('HOST')
user = os.environ.get('USER')
password = os.environ.get('PASSWORD')
database = os.environ.get('DATABASE')

print("正在获取环境变量...")
print(f"数据库地址：{host}")
print(f"数据库用户名：{user}")
print(f"数据库密码：{password}")
print(f"数据库名称：{database}")

# 连接数据库
connection = mysql.connector.connect(
    host=host,
    user=user,
    password=password,
    database=database
)

cursor = connection.cursor()

# 创建表（如果不存在）
create_table_query = """
CREATE TABLE IF NOT EXISTS ningm_price_monitor (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_id VARCHAR(50) NOT NULL,
    platform VARCHAR(50) NOT NULL,
    title VARCHAR(500),
    current_price DECIMAL(10,2),
    original_price DECIMAL(10,2),
    discount_rate DECIMAL(5,2),
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_item_id (item_id),
    INDEX idx_platform (platform),
    INDEX idx_create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
"""
cursor.execute(create_table_query)

# 要监控的商品列表
items_to_monitor = [
    {'id': '100027401595', 'platform': 'jd'},
    {'id': '100016034400', 'platform': 'jd'},
    # 可以添加更多商品
]

# 删除旧数据（保留30天内的数据）
print("删除旧数据...")
delete_query = "DELETE FROM ningm_price_monitor WHERE create_time < DATE_SUB(NOW(), INTERVAL 30 DAY)"
cursor.execute(delete_query)

# 获取并保存价格数据
print("开始获取商品价格数据...")
headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'
}

for item in items_to_monitor:
    try:
        if item['platform'] == 'jd':
            url = f"https://item.jd.com/prices/{item['id']}"
            response = requests.get(url, headers=headers)
            
            if response.status_code == 200:
                data = response.json()
                
                insert_query = """
                    INSERT INTO ningm_price_monitor 
                    (item_id, platform, current_price, original_price, discount_rate) 
                    VALUES (%s, %s, %s, %s, %s)
                """
                current_price = data.get('p', 0)
                original_price = data.get('op', 0)
                discount_rate = round((1 - current_price/original_price) * 100, 2) if original_price > 0 else 0
                
                insert_values = (
                    item['id'],
                    item['platform'],
                    current_price,
                    original_price,
                    discount_rate
                )
                cursor.execute(insert_query, insert_values)
                print(f"成功保存商品 {item['id']} 的价格数据")
                
    except Exception as e:
        print(f"获取商品 {item['id']} 的价格数据失败: {str(e)}")
        continue

connection.commit()
cursor.close()
connection.close()

print("数据保存完成！") 